import sqlite3
from command_parse import CommandParse
import logging

logging.basicConfig(level = logging.INFO,format = '%(asctime)s - %(name)s - %(levelname)s - %(message)s')

logger = logging.getLogger(__name__)

class DbMethod:

    def __enter__(self):

        return self
    
    def __exit__(self, exc_type, exc_val, exc_tb):

        if exc_type is None:

            self.conn.commit()
        self.cursor.close()
        self.conn.close()

    def __init__(self):

        self.conn = sqlite3.connect('sensor_data.db')

        self.conn.row_factory = self.dict_factory


        self.cursor = self.conn.cursor()

        self.init_db() # 初始化数据库

    def init_db(self):
        db_sql_file = "db.sql"

        with open(db_sql_file, 'r') as f:
            sql_statements = f.read()

        self.cursor.executescript(sql_statements)

    @staticmethod
    def dict_factory(cursor, row): # sqlite3 工厂函数

        d = {}

        for idx, col in enumerate(cursor.description):

            d[col[0]] = row[idx]
            
        return d  


    def insert_sensor_data(self, data):

        '''
        插入传感器交来的数据
        data: E5 1D 0000 12 0001 0021010012180908 00 00000000 64 00 00 00 02 0107 21EE
        '''

        cp = CommandParse(data)

        sensor_info = cp.parse_result

        devaddr = sensor_info["devaddr"]
        dev_type = sensor_info["dev_type"]

        logger.info(f"接收到：{data}")
        logger.info(f"devaddr:{devaddr} dev_type: {dev_type}")

        if dev_type in ["压力", "倾角"]:

            sql = "insert into sensor_info(sensor_address, sensor_type, row_command) VALUES (?,?,?)"

            self.cursor.execute(sql, (devaddr, dev_type, data))

    
    def get_all_sensor_info_by_now(self):

        '''获取当前所有的传感器数据'''

        sql = """\
            SELECT *
            FROM sensor_info AS s1
            WHERE 
                upload_time = (
                    SELECT MAX(upload_time)
                    FROM sensor_info AS s2
                    WHERE s1.sensor_address = s2.sensor_address
                )
                AND upload_time >= datetime('now', '-2 minutes');

        """

        self.cursor.execute(sql)

        return self.cursor.fetchall()
        